<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page trimDirectiveWhitespaces="true"%>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@page import="java.util.ArrayList"%>
<%@ page import = "org.codehaus.jackson.JsonGenerationException" %>
<%@ page import = "org.codehaus.jackson.map.JsonMappingException" %>
<%@ page import = "org.codehaus.jackson.map.ObjectMapper" %>
<%@ page import = "java.io.IOException" %>
<%@ page import = "java.io.File" %>
<%@ page import = "java.io.FileInputStream" %>
<%@ page import = "org.apache.poi.ss.usermodel.Cell" %>
<%@ page import = "org.apache.poi.ss.usermodel.Row" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import = "java.util.Iterator" %>
<%@ page import = "org.apache.poi.hssf.usermodel.HSSFDateUtil" %>
<%@ page import = "java.text.SimpleDateFormat" %>
<%@ page import = "java.util.Date" %>
<%@ page import = "java.util.Properties" %>
<%@ page import = "java.io.InputStream" %>

<%@ page import = "java.sql.PreparedStatement" %>

<% 
	String strUser = String.valueOf(session.getAttribute("sUser"));
	HttpSession htp_session = request.getSession();
			
	if (htp_session == null || htp_session.getAttribute("sUser") == null) {
	    // Forward the control to login.jsp if authentication fails or session expires
	    request.getRequestDispatcher("/login.jsp").forward(request,
	        response);
	}
	
	if("admin".equals(htp_session.getAttribute("sUser"))){%>
	<script>alert("This user dont have PERMISSION to access this zone.");</script>
	<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=admin_home.jsp"> 	
	<%}
			
	
		//////////get permission ///////////
		
		String name = String.valueOf(session.getAttribute("name"));
		String surname = String.valueOf(session.getAttribute("surname"));
		String role_name = String.valueOf(session.getAttribute("student_role"));
		
		String department_id = String.valueOf(session.getAttribute("department_id"));
	
		System.out.println("name : " + name);///////////////////////////////
		System.out.println("department_id : " + department_id);/////////////////////////
		
		
		List<String> system_name = new ArrayList<String>();
		
		Permission permission = new Permission(strUser,name);
		permission.setDepartmentId(department_id);
		permission.setStudentCreate(Boolean.valueOf(String.valueOf(session.getAttribute("student_create"))));
		permission.setStudentDelete(Boolean.valueOf(String.valueOf(session.getAttribute("student_delete"))));
		permission.setStudentRead(Boolean.valueOf(String.valueOf(session.getAttribute("student_read"))));
		permission.setStudentUpdate(Boolean.valueOf(String.valueOf(session.getAttribute("student_update"))));
		
		String permiss = "";
		if(permission.getStudentCreate()) {
			permiss = permiss + "Add";
		}
		if(permission.getStudentUpdate()) {
			if("".equals(permiss)) {
				permiss = permiss + "Edit";
			} else {
				permiss = permiss + " Edit";
			}
		}
		if(permission.getStudentRead()) {
			if("".equals(permiss)) {
				permiss = permiss + "Read";
			} else {
				permiss = permiss + " Read";
			}
		}
		if(permission.getStudentDelete()) {
			if("".equals(permiss)) {
				permiss = permiss + "Delete";
			} else {
				permiss = permiss + " Delete";
			}
		}
		//check permission
		if(!permission.getStudentCreate()){%>
			<script>alert("This user dont have PERMISSION to access this zone.");</script>
			<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=student_home.jsp"> 	
		<%}

	//set Database Connection
	String hostProps = "";
	String usernameProps  = "";
	String passwordProps  = "";
	String databaseProps = "";
	
	try {
		//get current path
		ServletContext servletContext = request.getSession().getServletContext();
		
		InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
		Properties props = new Properties();
		
		props.load(input);

		hostProps  = props.getProperty("host");
		usernameProps  = props.getProperty("username");
		passwordProps  = props.getProperty("password");
		databaseProps = props.getProperty("database");
	} catch (Exception e) { 
		out.println(e);  
	}
	
	// connect database
	Connection connect = null;		
	try {
		Class.forName("com.mysql.jdbc.Driver");
	
		connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
				"?user=" + usernameProps  + "&password=" + passwordProps + "&characterEncoding=tis620" );
	
		if(connect != null){
			System.out.println("Database Connect Sucesses.");
		} else {
			System.out.println("Database Connect Failed.");	
		}

	} catch (Exception e) {
		out.println(e.getMessage());
		e.printStackTrace();
	}
	




	System.out.println("student_add_normal_background Active");
	
	
	java.util.Date dt = new java.util.Date();

	java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd",java.util.Locale.US);

	String date_now = sdf.format(dt);
	
	String personal_id =  request.getParameter("personal_id");
	String title_th =  new String(request.getParameter("title_th").getBytes("ISO8859_1"),"UTF-8");/////////////EDIT////////////////
	String name_th =  request.getParameter("name_th");	
	String surname_th =  request.getParameter("surname_th");
	String name_en =  request.getParameter("name_en");	
	String surname_en =  request.getParameter("surname_en");
	String gender =  request.getParameter("gender");
	String marital_status = request.getParameter("marital_status"); //null
	
	String address =  request.getParameter("address"); //null
	String tel =  request.getParameter("tel"); //null'
	String email = request.getParameter("email");//null
	String facebook = request.getParameter("facebook");//null

	String father_name = request.getParameter("father_name");//null
	String father_address = request.getParameter("father_address");//null
	String father_tel = request.getParameter("father_tel");//null
	String mother_name = request.getParameter("mother_name");//null
	String mother_address = request.getParameter("mother_address");//null
	String mother_tel = request.getParameter("mother_tel");//null
	String parent_name = request.getParameter("parent_name");//null
	String parent_address = request.getParameter("parent_address");//null
	String parent_tel =request.getParameter("parent_tel");//null
	String student_id = request.getParameter("student_id");
	String entrance_id = request.getParameter("entrance_id");
	String school = request.getParameter("school");//null
	String gpa = request.getParameter("gpa");//null
	if(gpa == null || "null".equals(gpa) || "".equals(gpa)) {
		gpa = null;
	}
	
	String gpax = null;
	
	String dirty = "N";
	
	String start_date_in = request.getParameter("start_date");
	// end_date_in = request.getParameter("end_date");
	String date_of_birth_in = request.getParameter("date_of_birth");
	
	Date start_date_dt = sdf.parse(start_date_in);
	Date end_date_dt  = new Date();
		 end_date_dt.setYear(start_date_dt.getYear()+8);
		 end_date_dt.setMonth(start_date_dt.getMonth());
		 end_date_dt.setDate(start_date_dt.getDate());
	Date birth_date_dt  = sdf.parse(date_of_birth_in);
	



	String start_date= sdf.format(start_date_dt);
	String end_date =  sdf.format(end_date_dt);
	String birth_date = sdf.format(birth_date_dt);
	
	System.out.println("date_now:"+date_now.toString());
	System.out.println("start_date:"+start_date);
	System.out.println("end_date:"+end_date_dt);
	System.out.println("birth_date:"+birth_date);
	
	
	
	
	


	
	
	
	String status_id = request.getParameter("status_id");
	String lecturer_name_th = request.getParameter("lecturer_name");
	String lecturer_surname_th = request.getParameter("lecturer_surname");
	String curriculum_id = request.getParameter("curriculum_id");
	String department_id_sel = request.getParameter("department_id");
	
	
	String faculty_id = "1";
	String lecturer_id = "";
	String full_name_id ="";
	String title_en = null;
	
 	 
	
	
    switch(title_th){
	
	case "นาย" : title_en ="Mr."; break;
	case "นางสาว" : title_en ="Ms."; break;
	case "นาง" : title_en="Mrs."; break;
	}
	

		
		    
		    System.out.println("Phase1 Parameter");
		    System.out.println("name_th:"+name_th);
			System.out.println("surname_th:"+surname_th);
			String key_index_temp="";
			
		    ///////////////////Phase 1 // Check Existing data in People table 
			ResultSet rs_people =	connect.createStatement().executeQuery(" SELECT P.index "
															+ " FROM people P, full_name N "
															+ " WHERE P.index=N.index "
															+ " AND N.name_th='"+name_th+"'"
															+ " AND N.surname_th='"+surname_th+"'");
		    
				

    
			rs_people.last();
			if(rs_people.getRow() == 1) {	
				System.out.println("This people already exist in People Table");
				
				//////////////Check Existing data in Student table 
				ResultSet rs_old_student = connect.createStatement().executeQuery(" SELECT student_id "
																				 + " FROM student "
																				 + " WHERE student_id='"+student_id+"'");
				rs_old_student.last();
				if(rs_old_student.getRow()==1)
				{
					System.out.println("This person already exist in Student table");
					
					// 
				}else{
					// Add New Student 
				
		
					
					
					//Phase E 
					try{
						System.out.println("Phase E");
						ResultSet rs_lec = connect.createStatement().executeQuery(" SELECT L.index "
								+ " FROM lecturer L , full_name N "
								+ " WHERE L.index = N.index AND N.name_th='"+lecturer_name_th+"' AND N.surname_th='"+lecturer_surname_th+"'");
						rs_lec.last();
						if(rs_lec.getRow() == 1) {
							lecturer_id  = rs_lec.getString("index");
							System.out.println("Phase F Found lecturer_id:"+lecturer_id);
							
						
							// Phase F Add to student table
							try{
								
								System.out.println("Data Before Add Student");
								System.out.println("Entrance_id:"+entrance_id);
								System.out.println("Curriculum id:"+curriculum_id);
								System.out.println("Department_id:"+department_id);
								System.out.println("StudentId:"+student_id);
								System.out.println("lecturer_id:"+lecturer_id);
								System.out.println("Status id :"+status_id);
								System.out.println("start_date:"+start_date);	
								System.out.println("end_date:"+end_date);
								
								String sql = "INSERT INTO `student`(`index`,`student_id`,"+
										" `start_date`, `entrance_id`, `faculty_id`,`department_id`, `gpa`, `lecturer_id`,"+
										" `curriculum_id`, `status_id`, `school`, `gpax`, `father_name`,`father_address`, `father_phone`,"+
										" `mother_name`, `mother_address`, `mother_phone`, `parent_name`, `parent_address`, `parent_phone`,`end_date`)"
										+" VALUES ('"+key_index_temp+"','"+student_id+"','" + start_date + "', '"
										+ entrance_id + "', '" + faculty_id +"', "+ department_id + ", " + gpa + ", " 
										+ lecturer_id + "', "+ curriculum_id + ", '" + status_id + "', '" + school + "', '"
										+ gpax +", '"+father_name + "', '" + father_address + "', '" + father_tel + "', '" 
										+ mother_name + "', '" + mother_address + "', '" + mother_tel + "', '" 
										+ parent_name + "', '" + parent_address + "', '"+ parent_tel + "', '"+ end_date +"')";
								connect.createStatement().executeUpdate(sql);
								
								/* Log file */
								String log = strUser+" "+name+" "+surname+" add student";
								Log.writeUserFile(log);
								Log.writeUserFile(sql);								
						
							}catch(Exception z){
								System.out.println("Error At Phase F Add Student"+z);
								dirty="Y";
							
								
							}
							//END PHASE F	
							
							
							
						}else{
							System.out.println("Phase E error");
							dirty="Y";
				
							
						}
						
						
						
						
					}catch(Exception o){
						
						System.out.println("Error occer Phase E	:"+o);
						dirty="Y";
					
					}
					//
					
					
															
					
					
				}//END else New Student
				
				
				
				
			}else{
				System.out.println("Not Existing in People Table");
				
				
				String key_index = KeyGen.genTimeStamp();
				
				/////////////////////Phase 2 // Add full_name to full_name table
				System.out.println("Phase 2");
				System.out.println("key_index:"+key_index);
				System.out.println("Data before create full_name query");
				System.out.println(personal_id);
				System.out.println(title_th);
				System.out.println(title_en);
				System.out.println(name_th);
				System.out.println(name_en);
				System.out.println(surname_th);
				System.out.println(surname_en);
				System.out.println("gender:"+gender+":");
				System.out.println(date_now.toString());
				
			
					
				try{
					
					String sql = " INSERT INTO `full_name`(`index`,"+
							   " `title_th`, `title_en`, `name_th`, `name_en`, `surname_th`, "+
							   " `surname_en`, `date`) "+
							   " VALUES ('"+key_index+"',"+"'"+title_th+"', " +"'"+title_en+"', "+
							   " '"+name_th+"', "+"'"+name_en+"', "+ "'"+surname_th+"', "+"'"+surname_en+"', "+
							   " '"+date_now+"')";
					PreparedStatement pstmt = connect.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);  
					pstmt.executeUpdate();  
					ResultSet keys = pstmt.getGeneratedKeys();    
					keys.next();  
					int key = keys.getInt(1);
					
					full_name_id = Integer.toString(key);
					 
					/* Log file */
					String log = strUser+" "+name+" "+surname+" add student";
					Log.writeUserFile(log);
					Log.writeUserFile(sql);		
						
					System.out.println("Phase 2 Success with full_name_id :"+ key);
					
				
				}catch(Exception e){
					System.out.println("Error At Phase 2 Add Full_name:"+e);
					dirty="Y";
				
				} ///////////////////////////// End Phase 2 					
				
					
					// Phase 3 Add to people table 
					System.out.println("Data before Add People Table");	
					System.out.println("key_index:"+key_index);
					System.out.println("personalId:"+personal_id);
					System.out.println("ful_name_id:"+full_name_id);
					System.out.println("gender:"+gender);
					System.out.println("marital_status:"+marital_status);
					System.out.println("date_of_birth:"+birth_date.toString());
					System.out.println("phonenumber:"+tel);
					System.out.println("address:"+address);
					System.out.println("email:"+email);
					System.out.println("facebook:"+facebook);
					System.out.println("sql_statement:"+" INSERT INTO "+"`people`(`index`,`personal_id`, `full_name_id`,"+
							   " `gender`, `marital_status`, `date_of_birth`, `phone_number`,"+
							   " `address`, `email`, `facebook`)"+
							   " VALUES ('"+key_index+"','"+personal_id+"', '"+full_name_id+"', '"+gender+"', "+
							   " '"+marital_status+"', '"+birth_date+"', '"+tel+"', "+
							   " '"+address+"', '"+email+"', '"+facebook+"')");

					
					
				try{
					String sql = " INSERT INTO "+"`people`(`index`,`personal_id`, `full_name_id`,"+
							   " `gender`, `marital_status`, `date_of_birth`, `phone_number`,"+
							   " `address`, `email`, `facebook`)"+
							   " VALUES ('"+key_index+"','"+personal_id+"', '"+full_name_id+"', '"+gender+"', "+
							   " '"+marital_status+"', '"+birth_date+"', '"+tel+"', "+
							   " '"+address+"', '"+email+"', '"+facebook+"')";
					
					connect.createStatement().executeUpdate(sql);
					
					/* Log file */
					Log.writeUserFile(sql);		
					
					System.out.println("Phase 3 Success");
					

					//Phase 8
					
						try{
							System.out.println("Phase 8");
							ResultSet rs_lec = connect.createStatement().executeQuery(" SELECT L.index "
									+ " FROM lecturer L , full_name N "
									+ " WHERE L.index = N.index AND N.name_th='"+lecturer_name_th+"' AND N.surname_th='"+lecturer_surname_th+"'");
							rs_lec.last();
							if(rs_lec.getRow() == 1) {
								lecturer_id  = rs_lec.getString("index");
								System.out.println("Phase 9 Found lecturer_id:"+lecturer_id);
								
								// Phase 9 Add to student table
								try{
									
									System.out.println("Data Before Add Student");
									System.out.println("Entrance_id:"+entrance_id);
									System.out.println("Curriculum id:"+curriculum_id);
									System.out.println("Department_id_sel:"+department_id_sel);
									System.out.println("StudentId:"+student_id);
									System.out.println("lecturer_id:"+lecturer_id);
									System.out.println("Status id :"+status_id);
									System.out.println("start_date:"+start_date);	
									System.out.println("end_date:"+end_date);
	
									String sql_stu = "INSERT INTO `student`(`index`,`student_id`,"+
											" `start_date`, `entrance_id`, `faculty_id`,`department_id`, `gpa`, `lecturer_id`,"+
											" `curriculum_id`, `status_id`, `school`, `gpax`, `father_name`,`father_address`, `father_phone`,"+
											" `mother_name`, `mother_address`, `mother_phone`, `parent_name`, `parent_address`, `parent_phone`,`end_date`)"
											+" VALUES ('"+key_index+"','"+student_id+"', '" + start_date + "', '"
											+ entrance_id + "', '" + faculty_id +"', "+ department_id_sel + ", " + gpa + ", '" 
											+ lecturer_id + "', "+ curriculum_id + ", '" + status_id + "', '" + school + "', "
											+ gpax +", '"+father_name + "', '" + father_address + "', '" + father_tel + "', '" 
											+ mother_name + "', '" + mother_address + "', '" + mother_tel + "', '" 
											+ parent_name + "', '" + parent_address + "', '"+ parent_tel + "', '"+ end_date +"')";
									
									System.out.println(sql_stu);
									
									connect.createStatement().executeUpdate(sql_stu);
									
									/* Log file */
									Log.writeUserFile(sql_stu);
								}catch(Exception z){
									System.out.println("Error At Phase 9 Add Student"+z);
									dirty="Y";
									
								}
								//END PHASE 9
								
								
								
							}else{
								// lecturer not found
								dirty="Y";
						
								
								}
							
						}catch(Exception q){
							System.out.println("Phase 8 Error Occer:"+q);
							dirty="Y";
			
					//end Phase 8 
					
				
				}
					
				}catch(Exception y){
					System.out.println("Error At Phase 3 Add People:"+y);
					dirty="Y";
					
						
				}//End Phase 3
				
					

			
			if(("N").equals(dirty)){
				
				out.print("success");
			}else{
				out.println("fail");
			}
			
	}
			
	connect.close();
%>	
 